package com.tubruk.database;

import com.tubruk.object.Global;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private Cursor c;
	private SQLiteDatabase sqlDB;

	private boolean initialValue;
	
	//private static String DB_PATH = "/data/data/trax246/databases/";
	private static String DB_NAME = "traxDB135.db";
	
	private static final int DATABASE_VERSION = 1;
	
	//public static String TABLENAME_CONSTANT = "TRAX_CONSTANT";
	
	public class TABLE_MAPSUMMARY {
		public static final String TABLENAME = "TRAX_MAPSUMMARY";
		
		/*
		 	private double distance = 0.0;
			private float avgSpeed = 0;
			private float minSpeed = 0;
			private float maxSpeed = 0;
			private long timeStart = 0;
			private long timeStop = 0;
			private long timeSpent = 0;
		 */
		
		public static final String ID    			= "_id";
		public static final String TITLE 			= "title";
		public static final String TIMESTART		= "start";
		public static final String TIMESTOP			= "stop";
		public static final String TIMESPENT		= "spent";
		public static final String SPEEDMIN			= "min";
		public static final String SPEEDAVG			= "avg";
		public static final String SPEEDMAX			= "max";
		public static final String DISTANCE			= "distance";
		public static final String MODE				= "mode";
		public static final String DATATYPE			= "datatype";
		public static final String DATECREATED		= "datecreated";
		public static final String DATEMODIFIED		= "datemodified";
	}
	
	public class TABLE_MAPDETAIL {
		
		/*
		 	accuracy float
			altitude double
			bearing float
			latitude double
			longitude double
			provider string
			speed float
			time long
		 */
		public static final String TABLENAME		= "TRAX_MAPDETAIL";
		
		public static final String ID				= "_id";
		public static final String SUMMARY_ID		= "summary_id";
		public static final String ACCURACY			= "accuracy";
		public static final String ALTITUDE			= "altitude";
		public static final String LATITUDE			= "latitude";
		public static final String LONGITUDE		= "longitude";
		public static final String BEARING			= "bearing";
		public static final String PROVIDER			= "provider";
		public static final String SPEED			= "speed";
		public static final String TIME				= "time";
	}
	
	public class TABLE_CONSTANT {
		public static final int MODE_TITLE = -1;
		public static final int MODE_CHECKBOX = 0;
		public static final int MODE_LIST = 1;
		public static final int MODE_FREETEXT_INTEGER = 2;
		public static final int MODE_FREETEXT_STRING = 3;
		
		public static final int ID_PREFERENCE 		= 1;
		public static final int ID_WALK_DISTANCE 	= 3;
		public static final int ID_WALK_TIME		= 4;
		public static final int ID_BIKE_DISTANCE	= 6;
		public static final int ID_BIKE_TIME		= 7;
		public static final int ID_DRIVE_DISTANCE	= 9;
		public static final int ID_DRIVE_TIME		= 10;
			
		
		public static final String TABLENAME = "TRAX_CONSTANT";
		
		public static final String ID    			= "_id";
		public static final String TITLE 			= "title";
		public static final String DESCRIPTION 		= "description";
		public static final String MODE  			= "mode";
		public static final String VALUE 			= "val";
		
		
	}
	
	public class TABLE_CONSTANT_LIST {
		public static final String TABLENAME = "TRAX_CONSTANT_LIST";
		
		public static final String ID    = "_id";
		public static final String CONSTANT_ID = "constant_id";
		public static final String TITLE = "title";
		public static final String DESCRIPTION = "description";
	
		public static final int ID_PREFERENCE_WALK = 1;
		public static final int ID_PREFERENCE_BIKE = 2;
		public static final int ID_PREFERENCE_CAR = 3;
	
	}
	
	private SQLiteDatabase database;
	private Context context;
	
	public DatabaseHelper(){
		super(Global.activityContext, DB_NAME, null, DATABASE_VERSION);
		this.context = Global.activityContext;
		sqlDB = null;
		c = null;
		
		initialValue = false;
	}
	
	public DatabaseHelper(Context context){
		super (context, DB_NAME, null, DATABASE_VERSION);
		this.context = context;
		sqlDB = null;
		c = null;
		
		initialValue = false;
	}
	
	@Override
	public void onCreate (SQLiteDatabase db){
		db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_CONSTANT.TABLENAME + " (" + DatabaseHelper.TABLE_CONSTANT.ID + " INTEGER PRIMARY KEY, "
				+ DatabaseHelper.TABLE_CONSTANT.TITLE + " String, " + DatabaseHelper.TABLE_CONSTANT.DESCRIPTION + " String, " + DatabaseHelper.TABLE_CONSTANT.MODE + " INTEGER, " + DatabaseHelper.TABLE_CONSTANT.VALUE + " String)");
		
		db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_CONSTANT_LIST.TABLENAME + " (" + DatabaseHelper.TABLE_CONSTANT_LIST.ID + " INTEGER PRIMARY KEY, " 
				+ DatabaseHelper.TABLE_CONSTANT_LIST.CONSTANT_ID + " INTEGER, " + DatabaseHelper.TABLE_CONSTANT_LIST.TITLE + " String, " + DatabaseHelper.TABLE_CONSTANT_LIST.DESCRIPTION + " String)");
	
		db.execSQL("CREATE INDEX " + DatabaseHelper.TABLE_CONSTANT_LIST.TABLENAME + "_idx ON " + DatabaseHelper.TABLE_CONSTANT_LIST.TABLENAME 
				+ "(" + DatabaseHelper.TABLE_CONSTANT_LIST.CONSTANT_ID + ")");
		
		db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_MAPSUMMARY.TABLENAME + " (" + DatabaseHelper.TABLE_MAPSUMMARY.ID + " INTEGER PRIMARY KEY, "
				+ DatabaseHelper.TABLE_MAPSUMMARY.TITLE + " String, " + DatabaseHelper.TABLE_MAPSUMMARY.TIMESTART + " INTEGER, " + DatabaseHelper.TABLE_MAPSUMMARY.TIMESTOP + " INTEGER, " + DatabaseHelper.TABLE_MAPSUMMARY.TIMESPENT + " INTEGER, "
				+ DatabaseHelper.TABLE_MAPSUMMARY.SPEEDMIN + " REAL, " + DatabaseHelper.TABLE_MAPSUMMARY.SPEEDAVG + " REAL, " + DatabaseHelper.TABLE_MAPSUMMARY.SPEEDMAX + " REAL, "
				+ DatabaseHelper.TABLE_MAPSUMMARY.DISTANCE + " REAL, " + DatabaseHelper.TABLE_MAPSUMMARY.MODE + " INTEGER, " + DatabaseHelper.TABLE_MAPSUMMARY.DATATYPE + " INTEGER, "
				+ DatabaseHelper.TABLE_MAPSUMMARY.DATECREATED + " INTEGER, " + DatabaseHelper.TABLE_MAPSUMMARY.DATEMODIFIED + " INTEGER)");
		
		db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_MAPDETAIL.TABLENAME + " (" + DatabaseHelper.TABLE_MAPDETAIL.ID + " INTEGER PRIMARY KEY, "
				+ DatabaseHelper.TABLE_MAPDETAIL.SUMMARY_ID + " INTEGER, " + DatabaseHelper.TABLE_MAPDETAIL.ACCURACY + " REAL, " + DatabaseHelper.TABLE_MAPDETAIL.ALTITUDE + " REAL, " + DatabaseHelper.TABLE_MAPDETAIL.LATITUDE + " REAL, "
				+ DatabaseHelper.TABLE_MAPDETAIL.LONGITUDE + " REAL, " + DatabaseHelper.TABLE_MAPDETAIL.BEARING + " REAL, " + DatabaseHelper.TABLE_MAPDETAIL.PROVIDER + " String, " + DatabaseHelper.TABLE_MAPDETAIL.SPEED + " REAL, "
				+ DatabaseHelper.TABLE_MAPDETAIL.TIME + " INTEGER)");
		
		db.execSQL("CREATE INDEX " + DatabaseHelper.TABLE_MAPDETAIL.TABLENAME + "_idx ON " + DatabaseHelper.TABLE_MAPDETAIL.TABLENAME
				+ "(" + DatabaseHelper.TABLE_MAPDETAIL.SUMMARY_ID + ")");
		
		initialValue = true;
	}
	
	public void setupInitialValues(){
		
		if (initialValue) {
			ContentValues values = new ContentValues();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Preference");
			values.put(DatabaseHelper.TABLE_CONSTANT.DESCRIPTION, "Default mode on application start");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_LIST);
			values.put(DatabaseHelper.TABLE_CONSTANT.VALUE, Integer.toString(DatabaseHelper.TABLE_CONSTANT_LIST.ID_PREFERENCE_CAR));
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values );
			
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Walk");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_TITLE);
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values );
			
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Distance (m)");
			values.put(DatabaseHelper.TABLE_CONSTANT.DESCRIPTION, "Distance in meters");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_FREETEXT_INTEGER);
			values.put(DatabaseHelper.TABLE_CONSTANT.VALUE, "5");
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values);
	
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Time (s)");
			values.put(DatabaseHelper.TABLE_CONSTANT.DESCRIPTION, "Time in seconds");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_FREETEXT_INTEGER);
			values.put(DatabaseHelper.TABLE_CONSTANT.VALUE, "30");
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values);
	
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Bicycle");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_TITLE);
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values );
	
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Distance (m)");
			values.put(DatabaseHelper.TABLE_CONSTANT.DESCRIPTION, "Distance in meters");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_FREETEXT_INTEGER);
			values.put(DatabaseHelper.TABLE_CONSTANT.VALUE, "15");
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values);
	
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Time (s)");
			values.put(DatabaseHelper.TABLE_CONSTANT.DESCRIPTION, "Time in seconds");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_FREETEXT_INTEGER);
			values.put(DatabaseHelper.TABLE_CONSTANT.VALUE, "30");
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values);
	
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Car");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_TITLE);
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values );
	
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Distance (m)");
			values.put(DatabaseHelper.TABLE_CONSTANT.DESCRIPTION, "Distance in meters");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_FREETEXT_INTEGER);
			values.put(DatabaseHelper.TABLE_CONSTANT.VALUE, "100");
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values);
	
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Time (s)");
			values.put(DatabaseHelper.TABLE_CONSTANT.DESCRIPTION, "Time in seconds");
			values.put(DatabaseHelper.TABLE_CONSTANT.MODE, DatabaseHelper.TABLE_CONSTANT.MODE_FREETEXT_INTEGER);
			values.put(DatabaseHelper.TABLE_CONSTANT.VALUE, "60");
			this.insert(DatabaseHelper.TABLE_CONSTANT.TABLENAME, values);
			
			
			/* TABLE LIST */
			
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT_LIST.CONSTANT_ID, DatabaseHelper.TABLE_CONSTANT.ID_PREFERENCE);
			values.put(DatabaseHelper.TABLE_CONSTANT.TITLE, "Walk");
			this.insert(DatabaseHelper.TABLE_CONSTANT_LIST.TABLENAME, values);
			
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT_LIST.CONSTANT_ID, DatabaseHelper.TABLE_CONSTANT.ID_PREFERENCE);
			values.put(DatabaseHelper.TABLE_CONSTANT_LIST.TITLE, "Bicycle");
			this.insert(DatabaseHelper.TABLE_CONSTANT_LIST.TABLENAME, values);
			
			values.clear();
			values.put(DatabaseHelper.TABLE_CONSTANT_LIST.CONSTANT_ID, DatabaseHelper.TABLE_CONSTANT.ID_PREFERENCE);
			values.put(DatabaseHelper.TABLE_CONSTANT_LIST.TITLE, "Car");
			this.insert(DatabaseHelper.TABLE_CONSTANT_LIST.TABLENAME, values);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		//do nothing?
	}
	
	
	public long insert(String tableName, ContentValues values) {
        // get database to insert records
		SQLiteDatabase sqlDB = null;
		long rowId = -1;
		
		try {
		sqlDB = this.getWritableDatabase();
		
        // insert record in user table and get the row number of recently inserted record
        rowId = sqlDB.insert(tableName, "", values);
		}finally {
			this.close(sqlDB);
		}
        return rowId;
	}
	
	public int update(String tableName, ContentValues values, String whereClause, String[] whereArgs){
		// get database to insert records
		SQLiteDatabase sqlDB = null;
		int rowId = -1;
		
		try {
			sqlDB = this.getWritableDatabase();
			rowId = sqlDB.update(tableName, values, whereClause, whereArgs);
		}finally {
			this.close(sqlDB);
		}
        return rowId;
	}
	
	public int delete(String tableName, String selection, String[] selectionArgs) {
		SQLiteDatabase sqlDB = null;
		int result = -1;
		
		sqlDB = this.getWritableDatabase();
		result = sqlDB.delete(tableName, selection, selectionArgs);
       
		this.close(sqlDB);
		
		return result;
	}
	
	public Cursor query(String tableName, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        sqlDB = this.getReadableDatabase();
        qb.setTables(tableName);
        c = qb.query(sqlDB, projection, selection, null, null, null, sortOrder);
        
        return c;
    }
	
	public void close(SQLiteDatabase sqlDB){
		if (sqlDB != null){
			sqlDB.close();
			sqlDB = null;
		}
	}
	
	public void close(Cursor c){
		if (c != null){
			c.close();
			c = null;
		}
	}
	
	public void destroy(){
		this.close(c);
		this.close(sqlDB);
		this.close();
	}
}
